package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GoodUtil implements GoodDao {
	private Connection conn;
	private PreparedStatement goodQuery;
	private ResultSet rs;
	public List<Goods> getGoods() {
		List<Goods> goodList=new ArrayList<Goods>();
		try {
			conn=DBcon.getConnection();
			String sql="select id,name,price,detail,goodclass,number,brand,image from good order by id";
			goodQuery=conn.prepareStatement(sql);
			ResultSet rs=goodQuery.executeQuery();
			while(rs.next()) {
				Goods good=new Goods();
				good.setId(rs.getString("id"));
				good.setName(rs.getString("name"));
				good.setPrice(rs.getDouble("price"));
				good.setDetail(rs.getString("detail"));
				good.setGoodclass(rs.getString("goodclass"));
				good.setNumber(rs.getInt("number"));
				good.setBrand(rs.getString("brand"));
				good.setImage(rs.getString("image"));
				goodList.add(good);
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}
		finally {
			DBcon.closeConnection(conn);
			DBcon.closeResult(rs);
			DBcon.closeStatement(goodQuery);
		}
		return goodList;
	}

	public int add(Goods goodbean) {
		int result=0;
		try {
		conn=DBcon.getConnection();
		String sql="insert into good (id,name,price,detail,goodclass,number,brand,image) values(?,?,?,?,?,?,?,?)";
		goodQuery=conn.prepareStatement(sql);
		goodQuery.setString(1,goodbean.getId());
		goodQuery.setString(2,goodbean.getName());
		goodQuery.setDouble(3,goodbean.getPrice());
		goodQuery.setString(4,goodbean.getDetail());
		goodQuery.setString(5,goodbean.getGoodclass());
		goodQuery.setInt(6,goodbean.getNumber());
		goodQuery.setString(7, goodbean.getBrand());
		goodQuery.setString(8,goodbean.getImage());
		result=goodQuery.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			DBcon.closeConnection(conn);
			DBcon.closeResult(rs);
			DBcon.closeStatement(goodQuery);
		}
		return result;
	}

	public int delete(String id) {
		int result=0;
		try {
			conn=DBcon.getConnection();
			String sql="delete from good where id='"+id+"'";
			goodQuery=conn.prepareStatement(sql);
			result=goodQuery.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			DBcon.closeConnection(conn);
			DBcon.closeResult(rs);
			DBcon.closeStatement(goodQuery);
		}
		return result;
	}

	public int update(Goods goodbean) {
		int result=0;
		try {
			conn=DBcon.getConnection();
			String sql="update good set name=?,price=?,detail=?,goodclass=?,number=?,brand=?,image=? where id=?";
			goodQuery=conn.prepareStatement(sql);
			goodQuery.setString(1,goodbean.getName());
			goodQuery.setDouble(2,goodbean.getPrice());
			goodQuery.setString(3, goodbean.getDetail());
			goodQuery.setString(4,goodbean.getGoodclass());
			goodQuery.setInt(5,goodbean.getNumber());
			goodQuery.setString(6,goodbean.getBrand());
			goodQuery.setString(7,goodbean.getImage());
			goodQuery.setString(8,goodbean.getId());
			result=goodQuery.executeUpdate();
		}catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			DBcon.closeConnection(conn);
			DBcon.closeResult(rs);
			DBcon.closeStatement(goodQuery);
		}
		return result;
	}

	@Override
	public Goods findById(String id) {
		Goods good=null;
		try {
			conn=DBcon.getConnection();
			String sql="select * from good where id='"+id+"'";
			goodQuery=conn.prepareStatement(sql);
			rs=goodQuery.executeQuery();
			if(rs.next()) {
				good=new Goods();
				good.setId(rs.getString("id"));
				good.setName(rs.getString("name"));
				good.setPrice(rs.getDouble("price"));
				good.setDetail(rs.getString("detail"));
				good.setGoodclass(rs.getString("goodclass"));
				good.setNumber(rs.getInt("number"));
				good.setBrand(rs.getString("brand"));
				good.setImage(rs.getString("image"));
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			DBcon.closeConnection(conn);
			DBcon.closeResult(rs);
			DBcon.closeStatement(goodQuery);
		}
		
		return good;
	}

	@Override
	public List<Goods> find(String goodclass) {
		List<Goods> goodList=new ArrayList<Goods>();
		try {
			conn=DBcon.getConnection();
			String sql="select id,name,price,detail,goodclass,number,brand,image from good where goodclass='"+goodclass+"' or brand='"+goodclass+"'";
			goodQuery=conn.prepareStatement(sql);
			ResultSet rs=goodQuery.executeQuery();
			while(rs.next()) {
				Goods good=new Goods();
				good.setId(rs.getString("id"));
				good.setName(rs.getString("name"));
				good.setPrice(rs.getDouble("price"));
				good.setDetail(rs.getString("detail"));
				good.setGoodclass(rs.getString("goodclass"));
				good.setNumber(rs.getInt("number"));
				good.setBrand(rs.getString("brand"));
				good.setImage(rs.getString("image"));
				goodList.add(good);
			}
		}catch(SQLException e) {
			e.printStackTrace();
		}
		finally {
			DBcon.closeConnection(conn);
			DBcon.closeResult(rs);
			DBcon.closeStatement(goodQuery);
		}
		return goodList;
	}

	

	

}
